package co.yixiang.yshop.module.crm.service.crmindex;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import co.yixiang.yshop.framework.common.enums.CommonStatusEnum;
import co.yixiang.yshop.framework.common.enums.ShopCommonEnum;
import co.yixiang.yshop.framework.security.core.util.SecurityFrameworkUtils;
import co.yixiang.yshop.module.crm.controller.admin.crmindex.vo.BrieCountVO;
import co.yixiang.yshop.module.crm.controller.admin.crmindex.vo.CrmIndexRespVO;
import co.yixiang.yshop.module.crm.controller.admin.crmindex.vo.RegionStatisticsVO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmbills.CrmBillsDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmbusiness.CrmBusinessDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmclues.CrmCluesDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmcontract.CrmContractDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmcontractreceivables.CrmContractReceivablesDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmcustomer.CrmCustomerDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmcustomercontacts.CrmCustomerContactsDO;
import co.yixiang.yshop.module.crm.dal.dataobject.crmrecord.CrmRecordDO;
import co.yixiang.yshop.module.crm.dal.mysql.crmbills.CrmBillsMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmbusiness.CrmBusinessMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmclues.CrmCluesMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmcontract.CrmContractMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmcontractreceivables.CrmContractReceivablesMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmcustomer.CrmCustomerMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmcustomercontacts.CrmCustomerContactsMapper;
import co.yixiang.yshop.module.crm.dal.mysql.crmrecord.CrmRecordMapper;
import co.yixiang.yshop.module.crm.enums.RelationEnum;
import co.yixiang.yshop.module.crm.service.crmclues.CrmCluesService;
import co.yixiang.yshop.module.system.api.user.AdminUserApi;
import co.yixiang.yshop.module.system.dal.dataobject.notice.NoticeDO;
import co.yixiang.yshop.module.system.dal.mysql.notice.NoticeMapper;
import co.yixiang.yshop.framework.ip.core.utils.AreaUtils;
import co.yixiang.yshop.framework.ip.core.Area;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.Comparator;

/**
 * 商机 Service 实现类
 *
 * @author yshop
 */
@Service
@Validated
public class CrmIndexServiceImpl implements CrmIndexService {

    @Resource
    private CrmBusinessMapper businessMapper;
    @Resource
    private CrmCustomerMapper customerMapper;
    @Resource
    private CrmCluesMapper crmCluesMapper;
    @Resource
    private CrmContractMapper contractMapper;
    @Resource
    private CrmContractReceivablesMapper contractReceivablesMapper;
    @Resource
    private CrmRecordMapper crmRecordMapper;
    @Resource
    private CrmCustomerContactsMapper customerContactsMapper;
    @Resource
    private NoticeMapper noticeMapper;
    @Resource
    private CrmBillsMapper billsMapper;

    @Resource
    private AdminUserApi adminUserApi;



    @Resource
    private CrmCluesService crmCluesService;

    @Override
    public CrmIndexRespVO getIndexCount(String relation) {
//        Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
//        Date todayStart = DateUtil.beginOfDay(new Date());
//        Date todayEnd = DateUtil.endOfDay(new Date());
//        Date day30 = DateUtil.offsetDay(new Date(),30);
//
//        Long followCustomerCount = customerMapper.selectCount(new LambdaQueryWrapper<CrmCustomerDO>()
//                .between(CrmCustomerDO::getNextTime,todayStart,todayEnd).eq(CrmCustomerDO::getOwnerUserId,loginUserId));
//        Long followBusinessCount = businessMapper.selectCount(new LambdaQueryWrapper<CrmBusinessDO>()
//                .between(CrmBusinessDO::getNextTime,todayStart,todayEnd).eq(CrmBusinessDO::getOwnerUserId,loginUserId));
//        Long followCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
//                .between(CrmCluesDO::getNextTime,todayStart,todayEnd).eq(CrmCluesDO::getOwnerUserId,loginUserId));
//        Long contractDueCount = contractMapper.selectCount(new LambdaQueryWrapper<CrmContractDO>()
//                .between(CrmContractDO::getEndTime,todayStart,day30).eq(CrmContractDO::getOwnerUserId,loginUserId));
//        Long customerDueCount = 0L;
//        Long contractReturnCount = contractMapper.selectCount(new LambdaQueryWrapper<CrmContractDO>()
//                .eq(CrmContractDO::getOwnerUserId,loginUserId).apply("money > return_money"));
//        Long myInvoiceCount = crmInvoiceMapper.selectCount(new LambdaQueryWrapper<CrmInvoiceDO>()
//                .eq(CrmInvoiceDO::getCreator,loginUserId));
//        Long productCount = storeProductMapper.selectCount();
//
//        Long contractCheckCount = contractMapper.selectCount(new LambdaQueryWrapper<CrmContractDO>()
//                .apply( "FIND_IN_SET ('" + loginUserId + "',flow_admin_id)"));
//        Long receivablesCheckCount = contractReceivablesMapper.selectCount(new LambdaQueryWrapper<CrmContractReceivablesDO>()
//                .apply( "FIND_IN_SET ('" + loginUserId + "',flow_admin_id)"));
//        Long invoiceCheckCount = crmInvoiceMapper.selectCount(new LambdaQueryWrapper<CrmInvoiceDO>()
//                .apply( "FIND_IN_SET ('" + loginUserId + "',flow_admin_id)"));

        // 获取统计数据
        BrieCountVO brieCountVO = getBrieCount(relation);

        return CrmIndexRespVO.builder()
//                .followCustomerCount(followCustomerCount)
//                .followBusinessCount(followBusinessCount)
//                .followCluesCount(followCluesCount)
//                .contractDueCount(contractDueCount)
//                .customerDueCount(customerDueCount)
//                .contractReturnCount(contractReturnCount)
//                .myInvoiceCount(myInvoiceCount)
//                .productCount(productCount)
//                .contractCheckCount(contractCheckCount)
//                .receivablesCheckCount(receivablesCheckCount)
//                .invoiceCheckCount(invoiceCheckCount)
                .brieCountVO(brieCountVO)
                // 添加七个新统计数据，直接从brieCountVO中获取
                .totalCluesCount(brieCountVO.getTotalCluesCount())
                .todayCluesCount(brieCountVO.getTodayCluesCount())
                .yesterdayCluesCount(brieCountVO.getYesterdayCluesCount())
                .followRecordCount(brieCountVO.getFollowRecordCount())
                .totalExpenditure(brieCountVO.getTotalExpenditure())
                .dailyExpenditure(brieCountVO.getDailyExpenditure())
                .maxDailyExpenditure(brieCountVO.getMaxDailyExpenditure())
                .latestBillingDate(brieCountVO.getLatestBillingDate())
                .latestPositiveBalanceTotal(brieCountVO.getLatestPositiveBalanceTotal())
                .ageDistribution(brieCountVO.getAgeDistribution())
                .build();
    }


    /**
     * 统计当天
     * @return
     */
    private BrieCountVO getBrieCount(String relation) {
        Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
        boolean isAdmin = loginUserId == 1L; // 判断是否是超级管理员（用户id为1）
        Date todayStart = DateUtil.beginOfDay(new Date());
        Date todayEnd = DateUtil.endOfDay(new Date());

        Date yesterdayStart = DateUtil.beginOfDay(DateUtil.yesterday());
        Date yesterdayEnd = DateUtil.endOfDay(DateUtil.yesterday());

        List<Long> ids = new ArrayList<>();
        Long adminId = SecurityFrameworkUtils.getLoginUserId();
        if(RelationEnum.MY.getValue().equals(relation)){
            ids.add(adminId);
        }else if(RelationEnum.SUB.getValue().equals(relation)){
            ids = adminUserApi.getUserListBySubordinateIds(adminId);
        }

        Long count01 = businessMapper.selectCount(new LambdaQueryWrapper<CrmBusinessDO>()
                .in(!ids.isEmpty(),CrmBusinessDO::getOwnerUserId,ids)
                .between(CrmBusinessDO::getCreateTime,todayStart,todayEnd));
        Long count001 = businessMapper.selectCount(new LambdaQueryWrapper<CrmBusinessDO>()
                .in(!ids.isEmpty(),CrmBusinessDO::getOwnerUserId,ids)
                .between(CrmBusinessDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per001 = "0";
        if(count001 > 0){
            per001 = NumberUtil.div(NumberUtil.sub(count01,count001),count001,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count02 = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                .in(!ids.isEmpty(),CrmCluesDO::getOwnerUserId,ids)
                .between(CrmCluesDO::getCreateTime,todayStart,todayEnd));
        Long count002 = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                .in(!ids.isEmpty(),CrmCluesDO::getOwnerUserId,ids)
                .between(CrmCluesDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per002 = "0";
        if(count002 > 0){
            per002 = NumberUtil.div(NumberUtil.sub(count02,count002),count002,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count03 = customerMapper.selectCount(new LambdaQueryWrapper<CrmCustomerDO>()
                .in(!ids.isEmpty(),CrmCustomerDO::getOwnerUserId,ids)
                .between(CrmCustomerDO::getCreateTime,todayStart,todayEnd));
        Long count003 = customerMapper.selectCount(new LambdaQueryWrapper<CrmCustomerDO>()
                .in(!ids.isEmpty(),CrmCustomerDO::getOwnerUserId,ids)
                .between(CrmCustomerDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per003 = "0";
        if(count003 > 0){
            per003 = NumberUtil.div(NumberUtil.sub(count03,count003),count003,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count04 = crmRecordMapper.selectCount(new LambdaQueryWrapper<CrmRecordDO>()
                .in(!ids.isEmpty(),CrmRecordDO::getCreator,ids)
                .between(CrmRecordDO::getCreateTime,todayStart,todayEnd));
        Long count004 = crmRecordMapper.selectCount(new LambdaQueryWrapper<CrmRecordDO>()
                .in(!ids.isEmpty(),CrmRecordDO::getCreator,ids)
                .between(CrmRecordDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per004 = "0";
        if(count004 > 0){
            per004 = NumberUtil.div(NumberUtil.sub(count04,count004),count004,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count05 = contractMapper.selectCount(new LambdaQueryWrapper<CrmContractDO>()
                .in(!ids.isEmpty(),CrmContractDO::getOwnerUserId,ids)
                .between(CrmContractDO::getCreateTime,todayStart,todayEnd));
        Long count005 = contractMapper.selectCount(new LambdaQueryWrapper<CrmContractDO>()
                .in(!ids.isEmpty(),CrmContractDO::getOwnerUserId,ids)
                .between(CrmContractDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per005 = "0";
        if(count005 > 0){
            per005 = NumberUtil.div(NumberUtil.sub(count05,count005),count005,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        QueryWrapper<CrmContractDO> queryWrapper06 = new QueryWrapper<>();
        queryWrapper06.select("sum(money) as count06");
        queryWrapper06.eq("to_days(create_time)","to_days(now())");
        queryWrapper06.in(!ids.isEmpty(),"owner_user_id",ids);
        CrmContractDO crmContractDO = contractMapper.selectOne(queryWrapper06);
        BigDecimal count06 = BigDecimal.ZERO;
        if(crmContractDO != null){
            count06 = crmContractDO.getCount06();
        }

        QueryWrapper<CrmContractDO> queryWrapper006 = new QueryWrapper<>();
        queryWrapper006.select("sum(money) as count006");
        queryWrapper006.eq("to_days(now())-to_days(create_time)",1);
        queryWrapper006.in(!ids.isEmpty(),"owner_user_id",ids);
        CrmContractDO crmContractDO006 = contractMapper.selectOne(queryWrapper006);
        BigDecimal count006 = BigDecimal.ZERO;
        if(crmContractDO006 != null){
            count006 = crmContractDO006.getCount006();
        }
        String per006 = "0";
        if(count006.compareTo(BigDecimal.ZERO) > 0){
            per006 = NumberUtil.div(NumberUtil.sub(count06,count006),count006,2)
                    .multiply(new BigDecimal("100")).toString();
        }


        QueryWrapper<CrmContractReceivablesDO> queryWrapper07 = new QueryWrapper<>();
        queryWrapper07.select("sum(money) as count07");
        queryWrapper07.eq("to_days(create_time)","to_days(now())");
        CrmContractReceivablesDO crmContractReceivablesDO = contractReceivablesMapper.selectOne(queryWrapper07);
        BigDecimal count07 = BigDecimal.ZERO;
        if(crmContractDO != null){
            count07 = crmContractReceivablesDO.getCount07();
        }
        QueryWrapper<CrmContractReceivablesDO> queryWrapper007 = new QueryWrapper<>();
        queryWrapper007.select("sum(money) as count007");
        queryWrapper007.eq("to_days(now())-to_days(create_time)",1);
        CrmContractReceivablesDO crmContractReceivablesDO007 = contractReceivablesMapper.selectOne(queryWrapper007);
        BigDecimal count007 = BigDecimal.ZERO;
        if(crmContractReceivablesDO007 != null){
            count007 = crmContractReceivablesDO007.getCount007();
        }
        String per007 = "0";
        if(count007.compareTo(BigDecimal.ZERO) > 0){
            per007 = NumberUtil.div(NumberUtil.sub(count07,count007),count007,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count08 = customerContactsMapper.selectCount(new LambdaQueryWrapper<CrmCustomerContactsDO>()
                .in(!ids.isEmpty(),CrmCustomerContactsDO::getOwnerUserId,ids)
                .between(CrmCustomerContactsDO::getCreateTime,todayStart,todayEnd));
        Long count008 = customerContactsMapper.selectCount(new LambdaQueryWrapper<CrmCustomerContactsDO>()
                .in(!ids.isEmpty(),CrmCustomerContactsDO::getOwnerUserId,ids)
                .between(CrmCustomerContactsDO::getCreateTime,yesterdayStart,yesterdayEnd));
        String per008 = "0";
        if(count008 > 0){
            per008 = NumberUtil.div(NumberUtil.sub(count08,count008),count008,2)
                    .multiply(new BigDecimal("100")).toString();
        }

        Long count09 = customerMapper.selectCount(new LambdaQueryWrapper<CrmCustomerDO>()
                .between(CrmCustomerDO::getDealTime,todayStart,todayEnd)
                .eq(CrmCustomerDO::getDealStatus, ShopCommonEnum.IS_STATUS_1.getValue())
                .eq(CrmCustomerDO::getCreator,loginUserId));
        Long count10 = customerMapper.selectCount(new LambdaQueryWrapper<CrmCustomerDO>()
                .between(CrmCustomerDO::getDealTime,todayStart,todayEnd)
                .eq(CrmCustomerDO::getDealStatus, ShopCommonEnum.IS_STATUS_0.getValue())
                .eq(CrmCustomerDO::getCreator,loginUserId));
        Long count11 = businessMapper.selectCount(new LambdaQueryWrapper<CrmBusinessDO>()
                .between(CrmBusinessDO::getDealTime,todayStart,todayEnd)
                .eq(CrmBusinessDO::getIsEnd, ShopCommonEnum.IS_STATUS_1.getValue())
                .eq(CrmBusinessDO::getCreator,loginUserId));
        Long count12 = businessMapper.selectCount(new LambdaQueryWrapper<CrmBusinessDO>()
                .between(CrmBusinessDO::getDealTime,todayStart,todayEnd)
                .eq(CrmBusinessDO::getIsEnd, ShopCommonEnum.IS_STATUS_0.getValue())
                .eq(CrmBusinessDO::getCreator,loginUserId));
        Long count13 = noticeMapper.selectCount(new LambdaQueryWrapper<NoticeDO>()
                .between(NoticeDO::getCreateTime,todayStart,todayEnd)
                .eq(NoticeDO::getStatus, CommonStatusEnum.ENABLE.getStatus()));

        // 1. 总线索数 - 根据用户权限区分查询范围
        Long totalCluesCount;
        if (isAdmin) {
            // 超级管理员查询所有线索
            totalCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<>());
        } else {
            // 普通用户只查询负责人是自己的线索
            totalCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                    .eq(CrmCluesDO::getOwnerUserId, loginUserId));
        }

        // 2. 今日新增线索数 - 基于日期过滤，管理员用创建时间，普通用户用分配时间
        Long todayCluesCount;
        if (isAdmin) {
            // 超级管理员查询当天创建的所有线索
            todayCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                    .between(CrmCluesDO::getCreateTime, todayStart, todayEnd));
        } else {
            // 普通用户查询当天分配给自己的线索
            todayCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                    .between(CrmCluesDO::getAssignTime, todayStart, todayEnd)
                    .eq(CrmCluesDO::getOwnerUserId, loginUserId));
        }

        // 3. 昨日新增线索数 - 同上，但日期是昨天
        Long yesterdayCluesCount;
        if (isAdmin) {
            // 超级管理员查询昨天创建的所有线索
            yesterdayCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                    .between(CrmCluesDO::getCreateTime, yesterdayStart, yesterdayEnd));
        } else {
            // 普通用户查询昨天分配给自己的线索
            yesterdayCluesCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<CrmCluesDO>()
                    .between(CrmCluesDO::getAssignTime, yesterdayStart, yesterdayEnd)
                    .eq(CrmCluesDO::getOwnerUserId, loginUserId));
        }

        // 4. 跟进记录 - 只统计今天的跟进记录，超级管理员查所有，普通用户只查自己创建的
        Long followRecordCount;
        if (isAdmin) {
            // 超级管理员查询今天所有跟进记录
            followRecordCount = crmRecordMapper.selectCount(new LambdaQueryWrapper<CrmRecordDO>()
                    .between(CrmRecordDO::getCreateTime, todayStart, todayEnd));
        } else {
            // 普通用户只查询自己今天创建的跟进记录
            followRecordCount = crmRecordMapper.selectCount(new LambdaQueryWrapper<CrmRecordDO>()
                    .between(CrmRecordDO::getCreateTime, todayStart, todayEnd)
                    .eq(CrmRecordDO::getCreator, loginUserId));
        }

        // 5. 历史总花费 - 查询账单表中所有花费的总和，保留2位小数
        QueryWrapper<CrmBillsDO> totalExpenditureQuery = new QueryWrapper<>();
        totalExpenditureQuery.select("COALESCE(SUM(expenditure), 0) as total_expenditure");
        Map<String, Object> totalExpenditureMap = billsMapper.selectMaps(totalExpenditureQuery).get(0);
        BigDecimal totalExpenditure = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        if (totalExpenditureMap != null && totalExpenditureMap.get("total_expenditure") != null) {
            totalExpenditure = new BigDecimal(totalExpenditureMap.get("total_expenditure").toString()).setScale(2, RoundingMode.HALF_UP);
        }

        // 查询最早的账单日期，用于计算日均消费
        QueryWrapper<CrmBillsDO> firstBillQuery = new QueryWrapper<>();
        firstBillQuery.select("MIN(billing_date) as first_date");
        firstBillQuery.isNotNull("billing_date");
        BigDecimal dailyExpenditure = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);

        List<Map<String, Object>> firstBillList = billsMapper.selectMaps(firstBillQuery);
        if (firstBillList != null && !firstBillList.isEmpty() && firstBillList.get(0) != null) {
            Map<String, Object> firstBillMap = firstBillList.get(0);
            // 6. 日均消费 - 历史总花费除以(今天-最早账单日期)的天数，保留2位小数
            if (firstBillMap.get("first_date") != null) {
                Date firstBillDate = DateUtil.parse(firstBillMap.get("first_date").toString());
                long daysBetween = DateUtil.betweenDay(firstBillDate, new Date(), true);
                if (daysBetween > 0) {
                    dailyExpenditure = NumberUtil.div(totalExpenditure, new BigDecimal(daysBetween), 2).setScale(2, RoundingMode.HALF_UP);
                }
            }
        }

        // 7. 最高单日消费 - 找出消费最高的单日总额，保留2位小数
        QueryWrapper<CrmBillsDO> maxDailyExpenditureQuery = new QueryWrapper<>();
        maxDailyExpenditureQuery.select("COALESCE(SUM(expenditure), 0) as day_total, billing_date");
        maxDailyExpenditureQuery.groupBy("billing_date");
        maxDailyExpenditureQuery.orderByDesc("day_total");
        maxDailyExpenditureQuery.last("limit 1");
        BigDecimal maxDailyExpenditure = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);

        List<Map<String, Object>> maxDailyExpenditureList = billsMapper.selectMaps(maxDailyExpenditureQuery);
        if (maxDailyExpenditureList != null && !maxDailyExpenditureList.isEmpty() && maxDailyExpenditureList.get(0) != null) {
            Map<String, Object> maxDailyExpenditureMap = maxDailyExpenditureList.get(0);
            if (maxDailyExpenditureMap.get("day_total") != null) {
                maxDailyExpenditure = new BigDecimal(maxDailyExpenditureMap.get("day_total").toString()).setScale(2, RoundingMode.HALF_UP);
            }
        }

        // 8. 最新余额统计：找到最新 billing_date，然后计算该日期下 balance>0 的总和
        QueryWrapper<CrmBillsDO> latestDateQuery = new QueryWrapper<>();
        latestDateQuery.select("MAX(billing_date) as latest_date");
        latestDateQuery.isNotNull("billing_date");
        LocalDate latestBillingDate = null;
        List<Map<String, Object>> latestDateList = billsMapper.selectMaps(latestDateQuery);
        if (latestDateList != null && !latestDateList.isEmpty() && latestDateList.get(0) != null && latestDateList.get(0).get("latest_date") != null) {
            // MyBatis 返回 java.sql.Timestamp/String，根据 DO 字段我们转换为 LocalDate
            Date latestDate = DateUtil.parse(latestDateList.get(0).get("latest_date").toString());
            latestBillingDate = DateUtil.toLocalDateTime(latestDate).toLocalDate();
        }

        BigDecimal latestPositiveBalanceTotal = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        if (latestBillingDate != null) {
            QueryWrapper<CrmBillsDO> latestBalanceSumQuery = new QueryWrapper<>();
            latestBalanceSumQuery.select("COALESCE(SUM(balance), 0) as balance_total");
            latestBalanceSumQuery.eq("billing_date", latestBillingDate);
            latestBalanceSumQuery.gt("balance", 0);
            List<Map<String, Object>> balanceSumList = billsMapper.selectMaps(latestBalanceSumQuery);
            if (balanceSumList != null && !balanceSumList.isEmpty() && balanceSumList.get(0) != null && balanceSumList.get(0).get("balance_total") != null) {
                latestPositiveBalanceTotal = new BigDecimal(balanceSumList.get(0).get("balance_total").toString()).setScale(2, RoundingMode.HALF_UP);
            }
        }


        // 线索分类统计
        Map<String, Long> channelDistribution = getChannelDistribution(loginUserId);
        Map<String, Long> incomeDistribution = getIncomeDistribution(loginUserId);
        Map<String, Long> educationDistribution = getEducationDistribution(loginUserId);
        Map<String, Long> genderDistribution = getGenderDistribution(loginUserId);
        Map<String, Long> marriageDistribution = getMarriageDistribution(loginUserId);
        Map<String, Long> ageDistribution = getAgeDistribution(loginUserId);


        return BrieCountVO.builder()
                .count01(count01).count001(count001).per001(per001)
                .count02(count02).count002(count002).per002(per002)
                .count03(count03).count003(count003).per003(per003)
                .count04(count04).count004(count004).per004(per004)
                .count05(count05).count005(count005).per005(per005)
                .count06(count06).count006(count006).per006(per006)
                .count07(count07).count007(count007).per007(per007)
                .count08(count08).count008(count008).per008(per008)
                .count09(count09)
                .count10(count10)
                .count11(count11)
                .count12(count12)
                .count13(count13)
                // 添加七个新的统计数据
                .totalCluesCount(totalCluesCount)
                .todayCluesCount(todayCluesCount)
                .yesterdayCluesCount(yesterdayCluesCount)
                .followRecordCount(followRecordCount)
                .totalExpenditure(totalExpenditure)
                .dailyExpenditure(dailyExpenditure)
                .maxDailyExpenditure(maxDailyExpenditure)
                .latestBillingDate(latestBillingDate)
                .latestPositiveBalanceTotal(latestPositiveBalanceTotal)
                .channelDistribution(channelDistribution)
                .incomeDistribution(incomeDistribution)
                .educationDistribution(educationDistribution)
                .genderDistribution(genderDistribution)
                .marriageDistribution(marriageDistribution)
                .ageDistribution(ageDistribution)
                .build();


    }

    private Map<String, Long> getChannelDistribution(Long loginUserId) {
        return crmCluesService.getCluesChannelDistribution(loginUserId);
    }

    private Map<String, Long> getIncomeDistribution(Long loginUserId) {
        return crmCluesService.getCluesIncomeDistribution(loginUserId);
    }

    private Map<String, Long> getEducationDistribution(Long loginUserId) {
        return crmCluesService.getCluesEducationDistribution(loginUserId);
    }

    private Map<String, Long> getGenderDistribution(Long loginUserId) {
        return crmCluesService.getCluesGenderDistribution(loginUserId);
    }

    private Map<String, Long> getMarriageDistribution(Long loginUserId) {
        return crmCluesService.getCluesMarriageDistribution(loginUserId);
    }

    private Map<String, Long> getAgeDistribution(Long loginUserId) {
        return crmCluesService.getCluesAgeDistribution(loginUserId);
    }

    @Override
    public RegionStatisticsVO getRegionStatistics(String relation) {
        Long loginUserId = SecurityFrameworkUtils.getLoginUserId();
        boolean isAdmin = loginUserId == 1L; // 判断是否是超级管理员（用户id为1）

        List<Long> ids = new ArrayList<>();
        Long adminId = SecurityFrameworkUtils.getLoginUserId();
        if(RelationEnum.MY.getValue().equals(relation)){
            ids.add(adminId);
        }else if(RelationEnum.SUB.getValue().equals(relation)){
            ids = adminUserApi.getUserListBySubordinateIds(adminId);
        }

        // 查询线索数据 - 只要求城市编码不为空
        LambdaQueryWrapper<CrmCluesDO> queryWrapper = new LambdaQueryWrapper<>();
        if (isAdmin) {
            // 超级管理员查询所有线索
        } else {
            // 普通用户只查询负责人是自己的线索
            queryWrapper.eq(CrmCluesDO::getOwnerUserId, loginUserId);
        }
        queryWrapper.isNotNull(CrmCluesDO::getCity);
        
        List<CrmCluesDO> cluesList = crmCluesMapper.selectList(queryWrapper);

        // 统计总数
        Long totalCount = crmCluesMapper.selectCount(new LambdaQueryWrapper<>());
        Long regionCount = (long) cluesList.size();
        Long noRegionCount = totalCount - regionCount;

        // 城市统计 - 按城市编码统计
        Map<Integer, Long> cityCountMap = cluesList.stream()
                .filter(clues -> clues.getCity() != null)
                .collect(Collectors.groupingBy(CrmCluesDO::getCity, Collectors.counting()));

        List<RegionStatisticsVO.CityStatistics> cityStatistics = cityCountMap.entrySet().stream()
                .map(entry -> {
                    Integer cityCode = entry.getKey();
                    Area city = AreaUtils.getArea(cityCode);
                    
                    String cityName = city != null ? city.getName() : "未知城市";
                    String provinceName = "未知省份";
                    Integer provinceCode = null;
                    
                    // 尝试获取省份信息
                    if (city != null && city.getParent() != null) {
                        Area province = AreaUtils.getArea(city.getParent().getId());
                        if (province != null) {
                            provinceName = province.getName();
                            provinceCode = city.getParent().getId();
                        }
                    }
                    
                    String percentage = regionCount > 0 ? 
                            NumberUtil.div(entry.getValue(), regionCount, 4)
                                    .multiply(new BigDecimal("100")).toString() : "0";
                    
                    return RegionStatisticsVO.CityStatistics.builder()
                            .cityCode(cityCode)
                            .cityName(cityName)
                            .provinceCode(provinceCode)
                            .provinceName(provinceName)
                            .count(entry.getValue())
                            .percentage(percentage)
                            .build();
                })
                .sorted(Comparator.comparing(RegionStatisticsVO.CityStatistics::getCount).reversed())
                .collect(Collectors.toList());

        // 生成地图数据格式 - 使用城市数据，不包含坐标信息
        List<RegionStatisticsVO.MapDataItem> mapData = cityCountMap.entrySet().stream()
                .map(entry -> {
                    Integer cityCode = entry.getKey();
                    Area city = AreaUtils.getArea(cityCode);
                    String cityName = city != null ? city.getName() : "未知城市";
                    
                    return RegionStatisticsVO.MapDataItem.builder()
                            .name(cityName)
                            .code(cityCode.toString())
                            .value(entry.getValue())
                            .build();
                })
                .collect(Collectors.toList());

        return RegionStatisticsVO.builder()
                .provinceStatistics(new ArrayList<>()) // 空列表
                .cityStatistics(cityStatistics)
                .areaStatistics(new ArrayList<>()) // 空列表
                .mapData(mapData)
                .totalCount(totalCount)
                .regionCount(regionCount)
                .noRegionCount(noRegionCount)
                .build();
    }

}
